import collections
import openpyxl
from datetime import date
import matplotlib.pyplot as plt
import datetime
import pandas as pd
import matplotlib.ticker as mtick
import statistics
import os
import warnings
from predict_tool import *

# 用sort比较 可以是 -》  keys = dic,keys()  sorted(keys,key=compareTime) -》
#  每一行数据都init一下所有都columns  柜号直接存在dic，分开两个dic，一个供应商1，一个供应商2，再另外写一个tool方法，判断这个key在不在dic中
if __name__ == '__main__':
    skuList = pd.read_excel('./data/skuList.xls')
    skuList = skuList[skuList['Type'] == 'Inventory']
    skuList = skuList[['Product/Service Name', 'Sales Description', 'Sales Price / Rate', 'Quantity On Hand', 'SKU']]
    # 到供应商一号之前的所有的货柜
    outputFileName = './output/predict.xlsx'
    skuDic = {}
    curTime = date.today()  # 今天时间
    curTimeVO = str(curTime.year) + "." + str(curTime.month) + '.' + str(curTime.day)
    # 拿进货单
    excelFile = pd.ExcelFile('./data/in.xlsx')
    sheetNames = excelFile.sheet_names
    cabinetDicOringin1 = {}
    cabinetDicOringin2 = {}
    # 根据进货单 先从进货单里拿eta etd，并且判断是几号供应商，再拿到所有的柜号
    for sheetName in sheetNames:
        received = False  # 这箱货柜到货了没
        cabinetLoading = False  # 这个柜子装柜了没
        sheetPurchase = pd.read_excel('./data/in.xlsx', header=None, sheet_name=sheetName)
        columnPre = sheetPurchase.columns
        names = ['sku', 'qty']
        fillSheetNameNewColumnsPadding(names, columnPre)
        # 先拿第一行数据
        firstRow = sheetPurchase.iloc[0]
        origin = str(firstRow[0])  # 供应商
        cabinet = firstRow[1]  # 柜号
        actuallyTime = ''
        if len(firstRow) >= 3:
            eta = firstRow[2]  # 到货时间
            # 有eta了代表肯定装柜了
            cabinetLoading = True
            if len(firstRow) >= 4:
                actuallyTime = firstRow[3]
        else:
            eta = datetimeToTime(curTime + relativedelta(months=3))
        sheetPurchase = sheetPurchase[2:]
        # 换columns名之前要先切片
        sheetPurchase.columns = names

        # 避免柜号填错
        # print(cabinet)
        if origin == '1' and not cabinet.__contains__('柜'):
            print(sheetName + '柜号数据有问题，请仔细甄别')
            continue
        # 每个柜子中 sku对应的qty数量
        cabinetDicTemp = {}
        for cabinetIdx, cabinetRow in sheetPurchase.iterrows():
            cabinetDicTemp[cabinetRow['sku']] = cabinetRow['qty']
        if actuallyTime is not None and actuallyTime != '':
            received = True  # 已经到货
            cabinetDicTemp['eta'] = actuallyTime
        else:
            cabinetDicTemp['eta'] = eta
        # 是否到货 是否装柜
        cabinetDicTemp['received'] = received
        cabinetDicTemp['cabinetLoading'] = cabinetLoading
        if origin == '1':
            cabinetDicOringin1[cabinet] = cabinetDicTemp
        else:
            cabinetDicOringin2[cabinet] = cabinetDicTemp

    # print(cabinetDicOringin1)
    # print(cabinetDicOringin2)
    # # 补上供应商1和供应商2的柜号
    _, originKeys1, originKeys2, Key1AndEta, Key2AndEta = fillColumns(skuList, cabinetDicOringin1,
                                                                      cabinetDicOringin2)
    # print(skuList.columns)

    # 表中的所有column
    storeColumns, predictColumns = getStoreAndPredictTime()
    preColumn = ['Product/Service Name', 'Sales Description', 'Sales Price / Rate', 'Quantity On Hand', 'SKU', 'UPC',
                 'Cost Price']
    transitColumn = ['in transit', '实际in transit', '未装柜']
    soldColumn = ['safey stock', 'monthly sales']
    # fillRemainColumns(skuList, storeColumns, predictColumns)
    # print(skuList.columns)
    # skuList.to_csv('xx.csv', index=False)

    # print(originKeys1)
    safeStore = 100
    sales = 150  # 后续做成类似expireTime的表格

    # 开始做两个进货单的前缀和
    # # preSumColumnList = getpreSumColumnList(curTime)
    calculateDateDic = {}
    detailColumn = getDetailColumns()
    # 前缀和 包括transit
    preSumOrigin1 = getPreSumPurchase(cabinetDicOringin1, calculateDateDic, '1')
    preSumOrigin1 = getPreSumPurchase(cabinetDicOringin2, preSumOrigin1, '2', True, detailColumn)
    # print(calculateDateDic)
    openyxl_createFile(outputFileName)
    wb = openpyxl.load_workbook(outputFileName)
    sheet = wb['Sheet']
    # 开始填数据
    outColumn = 2
    outRow = 1
    optionFirstRow(sheet, outColumn, outRow, storeColumns, predictColumns, preColumn, originKeys1,
                   originKeys2, transitColumn)
    outRow += 2
    # 填预测时间和下单时间
    optionStoreTimeAndPredictTime(sheet, outColumn, outRow, storeColumns, predictColumns, preColumn, Key1AndEta,
                                  Key2AndEta, transitColumn)
    outColumn = 2
    outRow += 1

    # 缺失的upc级cost price
    upcAndCostList = []

    # 在这里把skuList转成dic
    for idx, row in skuList.iterrows():
        row['UPC'] = ''
        row['Cost Price'] = ''
        skuDic[row['SKU']] = row
    # 加上销量UPC等
    salesDf = pd.read_excel('../expireTimeAndStoreFileUpload/storeAndExpireTime.xlsx')
    saleDic = getSaleDic(salesDf)
    # 开始填数据
    # print(saleDic.keys())
    optionData(sheet, outColumn, outRow, skuDic, storeColumns, predictColumns, preColumn, cabinetDicOringin1,
               cabinetDicOringin2, transitColumn, originKeys1, originKeys2, preSumOrigin1, saleDic)
    # optionPreAnd
    wb.save(outputFileName)
    # print(cabinetDicOringin1)
    # print(type(skuList))
    # print(skuList.columns)
    # 拿到进货单里 每个sku的所有数据-》calculateDateDic

    # 后续规划是 直接循环origin数据，拿到每个sku在每个月的库存数量，再减去当前时间下的销售量即可显示数据
    # 当前默认是 供应商/柜号/eta/actual received形式
